<%@ page language="java" contentType="text/json; charset=UTF-8"
    pageEncoding="UTF-8"%>

<%@page trimDirectiveWhitespaces="true"%>    
<%@ page import="java.sql.ResultSet" %>
<%@ page import="java.sql.SQLException" %>
<%@ page import="java.sql.Statement" %>
<%@ page import="java.sql.Connection" %>
<%@ page import="java.sql.DriverManager" %>
<%@ page language="java" import="java.lang.*" %>
<%@ page import = "java.sql.SQLException" %>
<%@ page import = "com.seniorproject.aims.*" %>
<%@ page import = "java.util.List" %>
<%@ page import="java.util.ArrayList"%>
<%@ page import = "org.codehaus.jackson.JsonGenerationException" %>
<%@ page import = "org.codehaus.jackson.map.JsonMappingException" %>
<%@ page import = "org.codehaus.jackson.map.ObjectMapper" %>
<%@ page import = "java.io.IOException" %>
<%@ page import = "java.util.Properties" %>
<%@ page import = "java.io.InputStream" %>

<%

	String strUser = String.valueOf(session.getAttribute("sUser"));
	HttpSession htp_session = request.getSession();
			
	if (htp_session == null || htp_session.getAttribute("sUser") == null) {
	    // Forward the control to login.jsp if authentication fails or session expires
	    request.getRequestDispatcher("/login.jsp").forward(request,
	        response);
	}
	
	if("admin".equals(htp_session.getAttribute("sUser"))){%>
	<script>alert("This user dont have PERMISSION to access this zone.");</script>
	<meta HTTP-EQUIV="Refresh" CONTENT="0; URL=admin_home.jsp"> 	
	<%}
	
	String department = request.getParameter("department");
	String start_date = request.getParameter("start_date");
	String year = request.getParameter("year");
	String term = request.getParameter("term");
	
	
	System.out.println("@ Background search enroll summary");
	System.out.println("department:"+department);
	System.out.println("start_date:"+start_date);
	System.out.println("year:"+year);
	System.out.println("term:"+term);
	
	String Json = "";
	ObjectMapper mapper = new ObjectMapper();
	
	//set Database Connection
	String hostProps = "";
	String usernameProps  = "";
	String passwordProps  = "";
	String databaseProps = "";
	
	try {
		//get current path
		ServletContext servletContext = request.getSession().getServletContext();
		
		InputStream input = servletContext.getResourceAsStream("/properties/connectDB.properties");
		Properties props = new Properties();
		
		props.load(input);

		hostProps  = props.getProperty("host");
		usernameProps  = props.getProperty("username");
		passwordProps  = props.getProperty("password");
		databaseProps = props.getProperty("database");
	} catch (Exception e) { 
		out.println(e);  
	}
	
	// connect database
	Statement statement = null;
	Connection connect = null;		
	try {
		Class.forName("com.mysql.jdbc.Driver");
	
		connect =  DriverManager.getConnection("jdbc:mysql://" + hostProps  + "/" + databaseProps +
				"?user=" + usernameProps  + "&password=" + passwordProps + "&characterEncoding=tis620");//////"&characterEncoding=tis620" Encoding Thai);
	
		if(connect != null){
			System.out.println("Database Connect Sucesses.");
			
			List<Enrollment> enrollment = new ArrayList<Enrollment>();
			ResultSet rs = 
					connect.createStatement().executeQuery(" SELECT DISTINCT course_id, S.student_id, grade, type_of_enrollment, N.name_th, N.surname_th "
							+ " FROM enrollment E, class C, student S, full_name N, people P "
							+ " WHERE E.class_id=C.class_id AND S.student_id=E.student_id "
							+ " AND S.index=P.index AND P.full_name_id=N.full_name_id "
							+ " AND S.department_id="+department+" AND C.year="+year
							+ " AND C.semester="+term+" AND S.start_date LIKE '"+start_date+"%' "
							+ " ORDER BY S.student_id, course_id");
			
			
			while(rs.next())
			{
				Enrollment enroll = new Enrollment();
				
				enroll.setStudent_name(rs.getString("name_th"));
				enroll.setStudent_surname(rs.getString("surname_th"));
				enroll.setStudent_id(rs.getString("student_id"));
				enroll.setCourse_id(rs.getString("course_id"));
				enroll.setGrade(rs.getString("grade"));
				enroll.setType(rs.getString("type_of_enrollment"));	
				
				enrollment.add(enroll);
				
			}		
			
			try {
				Json = mapper.writeValueAsString(enrollment); 
			} catch (JsonGenerationException ex) {
				ex.printStackTrace();
			} catch (JsonMappingException ex) {
				ex.printStackTrace();
			} catch (IOException ex) {
				ex.printStackTrace();
			}
			
			out.print(Json);
			
			System.out.println("Size of student list :"+ enrollment.size());//try//
			
		
			System.out.println("Json :"+ Json);	
			
			
		} else {
			System.out.println("Database Connect Failed.");	

		}

	} catch (Exception e) {
		out.println(e.getMessage());
		e.printStackTrace();
	}

	connect.close();
%>